using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using LitJson;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

public class ExcelHandle
{
    /// <summary>
    /// 当前页
    /// </summary>
    private static string current_page;
    /// <summary>
    /// 当前行
    /// </summary>
    private static int current_row;
    /// <summary>
    /// 当前单元格
    /// </summary>
    private static int current_cell;

    /// <summary>
    /// 读取Excel
    /// </summary>
    public static List<ExcelData> ReadExcel(string path)
    {
        List<ExcelData> result = new List<ExcelData>();

        // 获取路径下所有以xls、xlsx文件的绝对路径
        List<string> xls_files = Directory.GetFiles(path, "*.xls", SearchOption.AllDirectories).ToList();
        List<string> xlsx_files = Directory.GetFiles(path, "*.xlsx", SearchOption.AllDirectories).ToList();

        // 全部文件列表
        List<string> files = xls_files.Concat(xlsx_files).ToList();

        string current_file;
        // 遍历Excel并执行导出方法
        for (int i = 0; i < files.Count; i++)
        {
            current_file = files[i];
            try
            {
                if (current_file.IndexOf("~$") > -1) continue;
                result = result.Concat(ResolvingExcel(current_file)).ToList();
            }
            catch (Exception e)
            {
#if UNITY_EDITOR
                UnityEngine.Debug.LogException(e);
#endif
                LogTool.WriteError("解析" + current_file + "时报错\n" + "报错分页:" + current_page + "\n" + "错误列:" + current_row + "    错误行:" + current_cell + "\n" + e.Source + "\n" + e.Message);
            }
        }
        return result;
    }

    /// <summary>
    /// 解析Excel
    /// </summary>
    private static List<ExcelData> ResolvingExcel(string path)
    {
        List<ExcelData> result = new List<ExcelData>();
        FileInfo info = new FileInfo(path);
        List<ISheet> sheetList = new List<ISheet>();
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Reset();
        stopwatch.Start();
        //去掉文件只读属性
        info.Attributes &= ~FileAttributes.ReadOnly;
        using (FileStream fs = File.Open(path, FileMode.Open))
        {
            if (path.EndsWith(".xls"))
            {
                HSSFWorkbook book = new HSSFWorkbook(fs);
                int sheet_count = book.Workbook.NumSheets;
                for (int i = 0; i < sheet_count; i++)
                {
                    sheetList.Add(book.GetSheetAt(i));
                }
            }
            else if (path.EndsWith(".xlsx"))
            {
                XSSFWorkbook book2 = new XSSFWorkbook(fs);
                int sheet_count = book2.NumberOfSheets;
                for (int i = 0; i < sheet_count; i++)
                {
                    sheetList.Add(book2.GetSheetAt(i));
                }
            }

            if (sheetList.Count == 0)
            {
                LogTool.WriteError("解析错误！文件格式不支持！");
                stopwatch.Stop();
                fs.Dispose();
                fs.Close();
                return result;
            }

            for (int m = 0; m < sheetList.Count; m++)
            {
                ISheet sheet = sheetList[m];
                current_page = sheet.SheetName;

                ExcelData excel_data = new ExcelData
                {
                    name = sheet.SheetName
                };

                List<int> annotations = new List<int>();

                // 获取注释、类型、字段和主键
                for (int i = 0; i < 3; i++)
                {
                    current_row = i;
                    IRow row = sheet.GetRow(i);
                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        current_cell = j;
                        ICell cell = row.GetCell(j);
                        string str = cell == null ? "" : cell.ToString();
                        switch (i)
                        {
                            case 0:
                                if (str.StartsWith("#"))
                                {
                                    annotations.Add(j);
                                }
                                else
                                {
                                    excel_data.notes.Add(str);
                                }
                                break;
                            case 1:
                                if (annotations.IndexOf(j) < 0)
                                    excel_data.types.Add(str);
                                if (j == 0) excel_data.key_type = str;
                                break;
                            case 2:
                                if (annotations.IndexOf(j) < 0)
                                    excel_data.fields.Add(str);
                                if (j == 0) excel_data.key = str;
                                break;
                        }
                    }
                }

                // 获取数据
                for (int i = 3; i <= sheet.LastRowNum; i++)
                {
                    current_row = i;
                    IRow row = sheet.GetRow(i);
                    if (string.IsNullOrEmpty(row.GetCell(0).ToString()))
                    {
                        LogTool.WriteError(string.Format("{0}表第{1}行的主键为空,数据读取至{2}行", current_page, i + 1, i));
                        break;
                    }

                    List<object> value = new List<object>();
                    int index = 0;
                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        if (annotations.IndexOf(j) > -1) continue;
                        current_cell = j;
                        switch (excel_data.types[index])
                        {
                            case "string":
                            case "json":
                                row.Cells[j].SetCellType(CellType.String);
                                value.Add(row.Cells[j].StringCellValue);
                                break;
                            case "int":
                            case "long":
                                if (String.IsNullOrEmpty(row.Cells[j].ToString()))
                                {
                                    value.Add(0);
                                }
                                else
                                {
                                    value.Add(long.Parse(row.Cells[j].ToString()));
                                }
                                break;
                            case "float":
                            case "double":
                                if (String.IsNullOrEmpty(row.Cells[j].ToString()))
                                {
                                    value.Add(0);
                                }
                                else
                                {
                                    value.Add(double.Parse(row.Cells[j].ToString()));
                                }
                                break;
                            case "int[]":
                            case "long[]":
                            case "float[]":
                            case "double[]":
                            case "string[]":
                            case "number[]":
                            case "Array<number>":
                            case "Array<string>":
                                if (String.IsNullOrEmpty(row.Cells[j].ToString()))
                                {
                                    value.Add(new object[0]);
                                }
                                else
                                {
                                    value.Add(JsonMapper.ToObject<object[]>(row.Cells[j].ToString()));
                                }
                                break;
                            case "number":
                                string temp = row.Cells[j].ToString();
                                if (String.IsNullOrEmpty(temp))
                                {
                                    value.Add(0);
                                }
                                else if (temp.IndexOf(".") < 0 && temp.IndexOf("E") < 0)
                                {
                                    value.Add(long.Parse(row.Cells[j].ToString()));
                                }
                                else
                                {
                                    value.Add(double.Parse(row.Cells[j].ToString()));
                                }
                                break;
                            case "boolean":
                            case "bool":
                                row.Cells[j].SetCellType(CellType.Boolean);
                                value.Add(row.Cells[j].BooleanCellValue);
                                break;
                            case "int[][]":
                            case "long[][]":
                            case "float[][]":
                            case "double[][]":
                            case "string[][]":
                            case "number[][]":
                                if (String.IsNullOrEmpty(row.Cells[j].ToString()))
                                {
                                    value.Add(new object[0]);
                                }
                                else
                                {
                                    value.Add(ToArray2(row.Cells[j].ToString()));
                                }
                                break;
                            case "int[][][]":
                            case "long[][][]":
                            case "float[][][]":
                            case "double[][][]":
                            case "string[][][]":
                            case "number[][][]":
                                if (String.IsNullOrEmpty(row.Cells[j].ToString()))
                                {
                                    value.Add(new object[0]);
                                }
                                else
                                {
                                    value.Add(ToArray3(row.Cells[j].ToString()));
                                }
                                break;
                            default:
                                value.Add(row.Cells[j].ToString());
                                break;
                        }
                        index++;
                    }
                    excel_data.values.Add(value);
                }
                result.Add(excel_data);
            }
        }
        return result;
    }

    private static List<List<object>> ToArray2(string str)
    {
        // 转换为二维数组
        return JsonConvert.DeserializeObject<List<List<object>>>(str);
    }

    private static List<List<List<object>>> ToArray3(string str)
    {
        // 转换为三维数组
        return JsonConvert.DeserializeObject<List<List<List<object>>>>(str);
    }

}
